新機能『Amazon Redshift Spectrum』から Amazon Athena のテーブルを参照する

Amazon Redshift SpectrumはAmazon S3にあるデータに対して、Amazon Redshiftから直接クエリを投げる事が出来る機能です。前回のブログで Amazon Redshift Spectrumは RedshiftからS3のデータ用に外部スキーマと外部テーブルを定義すると Amazon Athena に自動的にデータベースとテーブルが追加されるということが判明しました。そこで、今回は逆に Amazon Redshift Spectrum が既存の Amazon Athena のテーブルを参照できるか試してみました。

RedshiftからS3のデータ用に外部スキーマと外部テーブルを定義してテーブルを参照する手順については以下のブログを参照してください。今回はこのブログの続編となります。

S3データを直接クエリ出来る新機能『Amazon Redshift Spectrum』を実際に試してみました

Spectrumの設定・利用手順

Spectrumの設定は Amazon Athena からデータ定義言語（DDL）コマンドを使用してデータベースとテーブルを定義した後、Amazon Redshift からデータ定義言語（DDL）コマンドを使用してテーブルを定義します。

ステップ1：Amazon Redshift 用の IAM ロールを作成する

バックエンドではAmazon Athenaと連携しているので、Amazon Athenaの外部データカタログとAmazon S3のデータファイルにアクセス持つIAMロールを作成してRedshiftクラスタに付与、もしくは権限を追加する必要があります。Amazon Redshiftでロールを使用する方法の詳細については、「IAMロールを使用したCOPYおよびUNLOAD操作の承認」を参照してください。

ここでは mySpectrumRole というロール名に、AmazonS3ReadOnlyAccess と AmazonAthenaFullAccessの２つのマネージドポリシーを付与しています。

ステップ2：IAM ロールをクラスタに関連付ける

Amazon Redshiftが外部データカタログとAmazon S3にアクセスすることを許可するIAMロールを作成したら、そのロールをAmazon Redshiftクラスタに関連付けます。ステップ１で作成した IAMロールの ロール ARN は外部テーブルを定義する際に利用します。

AWS Management Consoleにサインインし、Amazon Redshiftコンソール を開き、ステップ１で作成したmySpectrumRole を選択します。

ステップ3：Amazon Athena に データベースとテーブルを作成

Redshift から照会する Amazon Athena のデータベースとテーブルを作成します。

データベースの作成

データベースが存在しない場合はデータベースを作成します。

CREATE DATABASE IF NOT EXISTS spectrumdb;

テーブルの作成

後で以前のブログで作成した sales テーブルと比較できるように同じテーブル定義の sales_athena を作成します。

CREATE EXTERNAL TABLE IF NOT EXISTS spectrumdb.sales_athena (
salesid int,
listid int,
sellerid int,
buyerid int,
eventid int,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
 'serialization.format' = '\t',
 'field.delim' = '\t'
) LOCATION 's3://awssampledb/tickit/spectrum/sales/'
TBLPROPERTIES ('has_encrypted_data'='false');

ステップ4：Amazon Redshift に 外部スキーマの作成

外部スキーマは外部データカタログ内のデータベースを参照し、Amazon Athena が Amazon Redshift の代わりにAmazon S3にアクセスを認可するための IAMロールのARN を提供します。Amazon Redshift から Amazon Athena の Hiveメタストアを使用して、データベースを作成します。この例では、外部スキーマを作成するときにAmazon Redshiftでデータベースを作成します。

外部スキーマの作成

外部スキーマを作成するには、CREATE EXTERNAL SCHEMAコマンドを実行します。次のコマンドのIAMロールARNを、手順1で作成したロールARNに置き換えてから、SQLクライアントでコマンドを実行します。

cmdb=# create external schema spectrum
cmdb-# from data catalog
cmdb-# database 'spectrumdb'
cmdb-# iam_role 'arn:aws:iam::123456789012:role/mySpectrumRole';
CREATE SCHEMA

外部テーブルの作成は？

外部テーブルはすでに作成済みの Amazon Athena のテーブルを参照できるため、作成する必要はありません。

外部スキーマ名の一覧を取得できます。

cmdb=# select * FROM pg_external_schema pe join pg_namespace pn ON pe.esoid = pn.oid;
 esoid  | eskind |  esdbname  |                                esoptions                                | nspname  | nspowner | nspacl
--------+--------+------------+-------------------------------------------------------------------------+----------+----------+--------
 707623 |      1 | spectrumdb | {"IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"} | spectrum |      100 |
(1 row)

ステップ3で作成した sales_athena テーブルのテーブル定義を確認します。

cmdb=# \x
Expanded display is on.
cmdb=# select * from SVV_EXTERNAL_TABLES;
-[ RECORD 1 ]-----+--------------------------------------------------------------------------------------
schemaname | athena_schema
tablename | sales
location | s3://awssampledb/tickit/spectrum/sales
input_format | org.apache.hadoop.mapred.TextInputFormat
output_format | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serialization_lib | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
serde_parameters | {"field.delim":"\t","serialization.format":"\t"}
compressed | 0
parameters | {"EXTERNAL":"TRUE","transient_lastDdlTime":"1492843944"}
-[ RECORD 2 ]-----+--------------------------------------------------------------------------------------
schemaname | athena_schema
tablename | sales_athena
location | s3://awssampledb/tickit/spectrum/sales
input_format | org.apache.hadoop.mapred.TextInputFormat
output_format | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serialization_lib | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
serde_parameters | {"field.delim":"\t","serialization.format":"\t"}
compressed | 0
parameters | {"EXTERNAL":"TRUE","has_encrypted_data":"false","transient_lastDdlTime":"1492964468"}

ステップ4：Amazon S3でデータを照会する

SPECTRUM.SALESテーブルの行数を取得します。

Timing is on.
cmdb=# select count(*) from spectrum.sales_athena;
 count
--------
 172462
(1 row)

Time: 1211.174 ms

制限事項

執筆時点では、Amazon Redshift Spectrum がサポートしていない SerDeを利用した Amazon Athena のテーブルはエラーとなります。例えば、jsonのSerDe（org.openx.data.jsonserde.JsonSerDe）などを実行すると以下のようなエラーが出力されます。

cmawsteamdb=# select * from athena_default.json_athena;
ERROR: External Catalog Error: Unsupported file format. org.apache.hadoop.mapred.TextInputFormat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat org.openx.data.jsonserde.JsonSerDe
Time: 326.571 ms

まとめ

想定通り、Amazon Redshift Spectrumは、Amazon Athena のデータベース名に対応する外部スキーマを登録することで、Amazon Athena のテーブルに対してクエリーを実行できることを確認できました。すでに Amazon Redshift と Amazon Athenaをお使いの環境であれば、外部スキーマを定義するだけでこれらのテーブル間の結合や条件フィルタリング、集約が可能になりますのでご活用ください。